package queries;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

import connect.DBConnectionUtil;

public class Query2 extends JFrame {
	public Query2() {
		this.setBounds(300, 240, 500, 400);
		this.setLayout(null);
		JLabel label0 = new JLabel(
				"Enter patient id and testname to get the recommended medicine ");
		label0.setBounds(100, 10, 500, 30);
		this.add(label0);
		JLabel label1 = new JLabel("Pid:");
		JTextField pid = new JTextField();
		label1.setBounds(100, 50, 100, 30);
		pid.setBounds(200, 50, 200, 30);
		this.add(label1);
		this.add(pid);
		JLabel label2 = new JLabel("Tname");
		JTextField tname = new JTextField();
		label2.setBounds(100, 100, 100, 30);
		tname.setBounds(200, 100, 200, 30);
		this.add(label2);
		this.add(tname);

		JButton query = new JButton("Query");
		query.setBounds(200, 150, 100, 30);
		this.add(query);
		this.setTitle("Query2:Get recommended medicine");
		this.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
		JTable jtable = new JTable();
		JScrollPane pane = new JScrollPane(jtable);
		pane.setBounds(100, 200, 300, 100);
		this.add(pane);
		this.setVisible(true);
		query.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent arg0) {
				try {
					// 1.connect database
					Class.forName(DBConnectionUtil.DBDRIVER).newInstance();
					Connection con = DBConnectionUtil.getConnection();
					if (con != null) {
						System.out.println("Connect database successfully!");
					}
					PreparedStatement ps = con
							.prepareStatement("SELECT  * FROM Patient WHERE pid=?");
					ps.setInt(1, Integer.parseInt(pid.getText()));
					ResultSet rset = ps.executeQuery();
					if (!rset.next()) {
						JOptionPane.showMessageDialog(null,
								"Pid does not exsit!");
						return;
					}
					ps = con.prepareStatement("SELECT  * FROM Test WHERE tname=?");
					ps.setString(1, tname.getText());
					rset = ps.executeQuery();
					if (!rset.next()) {
						JOptionPane.showMessageDialog(null,
								"Tname does not exsit!");
						return;
					}
					// 2.query code
					ps = con.prepareStatement("SELECT  id FROM Recommends WHERE pid=? and tname=?");
					ps.setInt(1, Integer.parseInt(pid.getText()));
					ps.setString(2, tname.getText());
					rset = ps.executeQuery();

					DefaultTableModel tablemodel = new DefaultTableModel();
					Object[] columns = new Object[1];
					tablemodel.addColumn("Id");
					while (rset.next()) {

						columns[0] = rset.getString(1);
						tablemodel.addRow(columns);
					}
					rset.close();
					ps.close();
					jtable.setModel(tablemodel);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}

		});

	}
}
